<?php

/*
 * UserDAO Interface
*/
interface DAOInterface {

}

/*
 * mysqli UserDAO Implementation
*/
class DAOmySQLi implements DAOInterface {
	private $dbConnection;

	//Constructor
	public function __construct() {
		$this->dbConnection = $this->getDbConnection();
	}

	//Deconstructor
	public function __destruct() {
		$this->dbConnection->close();
	}

	//Open Connection
	private function getDbConnection() {
		$dbhost = "localhost";
		$dbuser = "root";
		$dbpassword = "r2d2x11y12";
		$dbname = "ti5_shop";
		$dbConnection = new mysqli($dbhost, $dbuser, $dbpassword, $dbname);
		//Show Errormessage if Problem detected
		if ($dbConnection->connect_error) {
			echo "<h2>".$TEXT['cds-error']."</h2>";
			die('Connect Error (' . $dbConnection->connect_errno . ') '. $dbConnection->connect_error);
		}
		$dbConnection->set_charset('utf8');
		return $dbConnection;
	}

	//Check Logindata
	public function getLogin($userLogin, $userPassword) {
		
		$result = $this->dbConnection->query(
		"SELECT userLogin, userPassword, userActive FROM LoginData WHERE userLogin = '$userLogin' AND userPassword = '$userPassword' AND userActive = 1 LIMIT 1");
		
		if ($result->num_rows === false)
			return 0;
		else if ($result->num_rows == 1)
			return 1;		
	}

	//Show all News
	public function getNews(){
		$result = $this->dbConnection->query(
		"SELECT newsID, title, story, userDisplayName, newsdate
		 FROM NewsEntries
		 INNER JOIN LoginData ON author = loginID
		 ORDER BY newsdate DESC");
		while($row = $result->fetch_array()){
			echo "<div class=\"ym-gbox-left ym-clearfix\">";
			echo "<section class=\"box info\">";
			echo "<div class=\"ym-wbox\">";
			echo "<h3 class=\"title\"><a href=\"#\">".htmlspecialchars($row['title'])."</a></h3>";
			echo "<p class=\"meta\"><span class=\"date\">Autor:&nbsp;".htmlspecialchars($row['userDisplayName'])."&nbsp;&bull;&nbsp;</span><span class=\"posted\">Datum:&nbsp;".htmlspecialchars($row['newsdate'])."&nbsp;</span></p>";
			echo "<div style=\"clear: both;\">&nbsp;</div>";
			echo "<div class=\"entry\">".htmlspecialchars($row['story'])."&nbsp;</div></div>";
			echo "</section>";
			echo "</div>";
		}
	}

	//Show all products of a category
	public function getProductsOfCategory($category){
		$result = $this->dbConnection->query(
			"SELECT *
			 FROM Product
			 INNER JOIN Category ON Product.catID = Category.catID
			 INNER JOIN Supplier ON Product.supplID = Supplier.supplID
			 WHERE Product.prodActive = 1 AND Category.catID = $category");
		while($row = $result->fetch_array()){
			echo "<div class=\"ym-gbox-left ym-clearfix\">";
			echo "<section class=\"box info\">";
			echo "<div class=\"ym-wbox\">";
			echo "<h3 class=\"prodName\"><a href=\"index.php?product=".htmlspecialchars($row['prodID'])."\">".htmlspecialchars($row['prodName'])."</a></h3>";
			echo "<p class=\"meta\"><span class=\"date\">Category:&nbsp;".htmlspecialchars($row['catName'])."&nbsp;&bull;&nbsp;</span><span class=\"supplier\">Supplier:&nbsp;<a href=\"index.php?supplier=".htmlspecialchars($row['supplID'])."\">".htmlspecialchars($row['supplName'])."</a>"."&nbsp;</span></p>";
			echo "<div style=\"clear: both;\">&nbsp;</div>";
			echo "<div class=\"prodDescriptionShort\">".htmlspecialchars($row['prodDescriptionShort'])."&nbsp;</div></div>";
			echo "</section>";
			echo "</div>";
		}
	}
	
	//Show all products of a supplier
	public function getProductsOfSupplier($supplier){
		$result = $this->dbConnection->query(
			"SELECT * FROM Product
			 INNER JOIN Category ON Product.catID = Category.catID
			 INNER JOIN Supplier ON Product.supplID = Supplier.supplID
			 WHERE Product.prodActive = 1 AND Supplier.supplID = $supplier");
		while($row = $result->fetch_array()){
			echo "<div class=\"ym-gbox-left ym-clearfix\">";
			echo "<section class=\"box info\">";
			echo "<div class=\"ym-wbox\">";
			echo "<h3 class=\"prodName\"><a href=\"index.php?product=".htmlspecialchars($row['prodID'])."\">".htmlspecialchars($row['prodName'])."</a></h3>";
			echo "<p class=\"meta\"><span class=\"date\">Category:&nbsp;".htmlspecialchars($row['catName'])."&nbsp;&bull;&nbsp;</span><span class=\"supplier\">Supplier:&nbsp;<a href=\"index.php?supplier=".htmlspecialchars($row['supplID'])."\">".htmlspecialchars($row['supplName'])."</a>"."&nbsp;</span></p>";
			echo "<div style=\"clear: both;\">&nbsp;</div>";
			echo "<div class=\"prodDescriptionShort\">".htmlspecialchars($row['prodDescriptionShort'])."&nbsp;</div></div>";
			echo "</section>";
			echo "</div>";
		}
	}
	
	//Show product
	public function getProductDetails($product){
		$result = $this->dbConnection->query(
			"SELECT prodName,
			 prodID,
			 Product.catID,
			 catName,
			 Product.supplID,
			 prodPrice,
			 prodMRSP,
			 supplName,
			 prodImage,
			 prodDescription,
			 statusName,
			 statusColor
			 FROM Product
			 INNER JOIN Category ON Product.catID = Category.catID
			 INNER JOIN Supplier ON Product.supplID = Supplier.supplID
			 INNER JOIN Status ON Product.statusID = Status.statusID
			 WHERE Product.prodID = $product
			");
		while($row = $result->fetch_array()){
			echo "<div class=\"ym-gbox-left ym-clearfix\">";
			echo "<section class=\"box info\">";
			echo "<div class=\"ym-wbox product\">";
			echo "<h3 class=\"product-title\"><a href=\"index.php?product=".htmlspecialchars($row['prodID'])."\">".htmlspecialchars($row['prodName'])."</a>";
			echo "&nbsp;<a href=\"generateXML.php?product=".htmlspecialchars($row['prodID'])."\"><img class=\"xmlicon\" border=\"0\" alt=\"(XML document)\" src=\"images/xml.gif\" /></a></h3>";
			echo "<p class=\"meta\"><span class=\"date\">Category:&nbsp;".htmlspecialchars($row['catName'])."&nbsp;&bull;&nbsp;</span><span class=\"supplier\">Supplier:&nbsp;<a href=\"index.php?supplier=".htmlspecialchars($row['supplID'])."\">".htmlspecialchars($row['supplName'])."</a>"."&nbsp;</span></p>";
			echo "<div style=\"clear: both;\">&nbsp;</div>";
			
			//Show product picture if there is one
			if(!$row['prodImage'])
				echo '<img class="product-image" src="upload/noPicture.png"/>';
			else
				echo '<img class="product-image" src="upload/' . htmlspecialchars($row['prodImage']) . '"/>';
			
			echo "<div class=\"prodDescription\">".htmlspecialchars($row['prodDescription'])."&nbsp;</div>";
			echo "<div style=\"clear: both;\">&nbsp;</div>";
			
			//Get product options if there are
			$this->getProductOptions($product);
			
			//Show MRSP if there is one
			if($row['prodMRSP'])
				echo ' statt: <strike style="color:red;"><span class="product-mrsp">$' . htmlspecialchars($row['prodMRSP']) . '</span></strike>';

			echo "<div class=\"product-status\">Lieferfrist: <span style=color:" . htmlspecialchars($row['statusColor']) . ";>" . htmlspecialchars($row['statusName']) ." </span></div><br/>";
			                        
			echo "<div role=\"button\" alt=\"Add to cart\" tabindex=\"0\" class=\"googlecart-add-button\"></div>";
			 
			echo "</div>";
			echo "</section>";
			echo "</div>";
		}
	}
	
	//Show all options of a product
	function getProductOptions($product) {
	
		//Size, Color etc.
		$result = $this->dbConnection->query(
					"SELECT DISTINCT
					 optValue,
					 prodPrice,
					 Product.prodID,
					 Product_Option.prodOptRequired
					 FROM Product 
					 INNER JOIN Product_Option ON Product.prodID = Product_Option.prodID 
					 WHERE Product.prodID = $product 
					 AND Product_Option.prodOptRequired = 1
					");
		
		//Show Values of an option, if there is an option like size (small, medium, big)
		if ($result->num_rows == true){
			while($row = $result->fetch_array()){
				$getOptions = $this->dbConnection->query(
					"SELECT
				 	 Product.prodID,
				 	 prodPrice,
				 	 prodOptPrice,
				 	 prodName,
				 	 optValue,
				 	 optValueName,
				 	 Product_Option.prodOptRequired
				 	 FROM Product
				 	 INNER JOIN Product_Option ON Product.prodID = Product_Option.prodID
				 	 INNER JOIN Product_Option_Value ON Product_Option.prodOptID = Product_Option_Value.prodOptID
				 	 INNER JOIN Option_Value ON Product_Option_Value.optValueID = Option_Value.optValueID
				 	 INNER JOIN OptionType ON Option_Value.optTypeID = OptionType.optTypeID
				 	 WHERE Product.prodID = $product AND optValue = '" . $row['optValue'] . "' AND prodOptRequired = 1
					");
				
				echo '<form name="product">';
				
				if ($getOptions->num_rows == true)
				{
						echo $row['optValue'] . ': <select name="Attribute" id="' . $row['optValue'] . '" onchange="javascript:Berechnen(' . $row['prodPrice'] . ')" class="product-attr-' . $row['optValue'] . '">';
						echo '<option>Bitte wählen</option>';
			
						while($row = $getOptions->fetch_array()){
							echo '<option value="+' . $row['prodOptPrice']  . ' (' . $row['optValueName'] . ')">' . $row['optValueName'] . ' (+$' . $row['prodOptPrice']  . ')</option>';
						}
						echo '</select><br />';	
				}
			}
			
			//Get basic price if there are options
			$getBasePrice = $this->dbConnection->query(
			"SELECT prodPrice 
			 FROM Product 
			 WHERE Product.prodID = $product
			");
		
			while($row = $getBasePrice->fetch_array()){
				$prodStartPrice = $row['prodPrice'];
			}
		
			echo '</form></br>';
			echo 'Preis: <span class="product-price">Bitte Option(en) wählen</span>';
		
		}
		else{
			//Get basic price if there are no options
			$getPrice = $this->dbConnection->query(
			"SELECT prodPrice 
			 FROM Product
			 WHERE prodID = $product
			");
			
			while($row = $getPrice->fetch_array()){
				echo 'Preis: <span class="product-price">$' . $row['prodPrice'] . '</span>';
			}
		}
			
		
	}
		
	
	//Show Menu with categories, subcategories and count items
	function getCategories() {
		$result = $this->dbConnection->query(
			"SELECT * 
			 FROM Category 
			 WHERE catParentID IS NULL OR catParentID = 0");
		
		echo "<nav class='ym-vlist'>";
		echo "<h6 class='ym-vtitle'>Produktgruppen</h6>";
		echo "<ul>";
		
		while($row = $result->fetch_array()){
			echo "<li><span>" .htmlspecialchars($row['catName']). "</span><ul>";
			$this->getSubCategories($row['catID']);
			echo "</ul></li>";
		}
		
		echo "</ul></nav>";
	}

	function getItemsInCategory($catID) {
		$result = $this->dbConnection->query(
			"SELECT prodID 
			 FROM Product 
			 WHERE catID = $catID AND prodActive = 1");
		
		$row_count = $result->num_rows;
		if ($row_count === false)
			return "0";
		else if ($row_count >=0)
			return $row_count;
	}

	function getSubCategories($catID) {
		$result = $this->dbConnection->query(
			"SELECT * 
			 FROM Category 
			 WHERE catParentID = $catID");
		
		while($row = $result->fetch_array()){
			$row_count = $this->getItemsInCategory($row['catID']);
			echo '<li><a href="index.php?category=' . $row['catID'] . '">' . $row['catName'] . ' (' . $row_count . ')</a></li>';
		}
	}


	//Show all logins
	public function getLoginData(){
		$result = $this->dbConnection->query(
		"SELECT * FROM LoginData;");				
		echo "<table class=\"delete\">";
		echo "<tr><th>ID</th><th>User login</th><th>User Displayname</th><th>User password</th><th>User active</th><th>Setdate</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['loginID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['userLogin'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['userDisplayName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['userPassword'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['userActive'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['setDate'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=login.php?action=dellogin&id=".$row['loginID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}


	//Delete entries


	//For delete_products: Suppliers
	public function getSupplier(){
		$result = $this->dbConnection->query(
		"SELECT supplID, supplName, supplActive FROM Supplier;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>Supplier ID</th><th>Supplier Name</th><th>Active</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['supplID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['supplName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['supplActive'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=delsupplier&id=".$row['supplID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//for delete_products: Status
	public function getStatus(){
		$result = $this->dbConnection->query(
		"SELECT statusID, statusName, statusColor FROM Status;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>Status ID</th><th>Status Name</th><th>Status Color</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['statusID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['statusName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['statusColor'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=delstatus&id=".$row['statusID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//for delete_products: Option Typ
	public function getOptionType(){
		$result = $this->dbConnection->query(
		"SELECT optTypeID, optType, optName FROM OptionType;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>Option Type ID</th><th>Option Type</th><th>Option Name</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['optTypeID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['optType'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['optName'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=deloptiontype&id=".$row['optTypeID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//For delete_products: Option Value
	public function getOptionValue(){
		$result = $this->dbConnection->query(
		"SELECT optValueID, optValueName, optTypeID FROM Option_Value;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>Option Value ID</th><th>Option Value Name</th><th>Option Type ID</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['optValueID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['optValueName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['optTypeID'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=deloptionvalue&id=".$row['optValueID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//For delete_products: Produkt Option
	public function getProductOption(){
		$result = $this->dbConnection->query(
		"SELECT prodOptID, prodID, optValue, prodOptRequired FROM Product_Option;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>Product Option ID</th><th>Product ID</th><th>Option Value</th><th>Product Option Required</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['prodOptID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['optValue'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodOptRequired'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=delproductoption&id=".$row['prodOptID'].">x</a></td>";
			echo "</tr>";

		}
		echo "</table>";
	}

	//For delete_products: Produkt Option Value
	public function getProductOptionValue(){
		$result = $this->dbConnection->query(
		"SELECT prodOptValueID, prodOptID, optValueID, prodOptQuantity, prodOptPrice FROM Product_Option_Value;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>Product Option Value ID</th><th>Product Option ID</th><th>Option Value ID</th><th>Product Option Quantity</th><th>Product Option Price</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['prodOptValueID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodOptID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['optValueID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodOptQuantity'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodOptPrice'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=delproductoptionvalue&id=".$row['prodOptValueID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//For delete_products: Category
	public function getCategory(){
		$result = $this->dbConnection->query(
		"SELECT 
		 catID, 
		 catParentID, 
		 catName, 
		 catDescriptionShort, 
		 catDescription, 
		 catImage, 
		 catActive 
		 FROM Category;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>cat ID</th><th>cat Parent ID</th><th>cat Name</th><th>cat Description Short</th><th>cat Description</th><th>cat Image</th><th>cat Active</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['catID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catParentID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catDescriptionShort'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catDescription'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catImage'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catActive'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=delcategory&id=".$row['catID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//For delete_products: Products
	public function getProduct(){
		$result = $this->dbConnection->query(
		"SELECT prodID, supplID, catID, prodName, prodDescriptionShort, prodDescription, prodImage, prodThumb, statusID, prodActive, prodMRSP, prodPrice FROM Product;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>prod ID</th><th>sup. ID</th><th>cat ID</th><th>prod Name</th><th>prod Description Short</th><th>prod Description</th><th>prod Image</th><th>prod Thumb</th><th>stat. ID</th><th>prod Act.</th><th>prod MRSP</th><th>prod Price</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['prodID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['supplID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['catID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodDescriptionShort'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodDescription'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodImage'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodThumb'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['statusID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodActive'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodMRSP'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['prodPrice'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_product.php?action=delproduct&id=".$row['prodID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}

	//For edit_news: Show all news entries
	public function getEditNews(){
		$result = $this->dbConnection->query(
		"SELECT 
		 newsID, 
		 title, 
		 story, 
		 userDisplayName, 
		 newsdate 
		 FROM NewsEntries
		 INNER JOIN LoginData on NewsEntries.author = LoginData.loginID;");		
		echo "<table class=\"delete\">";
		echo "<tr><th>ID</th><th>Titel</th><th>Author</th><th>Story</th><th>Entry date</th><th>Delete</th></tr>";
		while( $row = $result->fetch_array())
		{
			echo "<tr>";
			echo "<td>".htmlspecialchars($row['newsID'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['title'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['userDisplayName'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['story'])."&nbsp;</td>";
			echo "<td>".htmlspecialchars($row['newsdate'])."&nbsp;</td>";
			echo "<td class=\"xdelete\"><a class=\"xdelete\" href=edit_news.php?action=delnews&id=".$row['newsID'].">x</a></td>";
			echo "</tr>";
		}
		echo "</table>";
	}


	//Dropdown Menus


	public function getDropdown_NewsAuthor(){
		$result = $this->dbConnection->query(
		"SELECT * FROM LoginData;");		
		echo "<select name='newsAuthor' size='1' id='insert-dropdown'>";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['loginID'])."\">".htmlspecialchars($row['userDisplayName'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_supplID_Product(){
		$result = $this->dbConnection->query(
		"SELECT supplID, supplName FROM Supplier;");			
		echo "<select name=\"dropdown_supplID_Product\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['supplID'])."\">".htmlspecialchars($row['supplName'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_catID_Product(){
		$result = $this->dbConnection->query(
		"SELECT 
		C1.catID AS CatNo, 
		C1.catName AS Cat,
		C2.catName AS SubCat
		FROM Category AS C1
		INNER JOIN Category AS C2 ON C1.catParentID = C2.catID
		WHERE C1.catActive = 1 AND (C1.catParentID IS NOT NULL OR C1.catParentID != 0);");			
		echo "<select name=\"dropdown_catID_Product\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['CatNo'])."\">".htmlspecialchars($row['Cat'])."&nbsp;--->&nbsp;".htmlspecialchars($row['SubCat'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_statusID_Product(){
		$result = $this->dbConnection->query(
		"SELECT statusID, statusName FROM Status;");			
		echo "<select name=\"dropdown_statusID_Product\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['statusID'])."\">".htmlspecialchars($row['statusName'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_catID_Category(){
		$result = $this->dbConnection->query(
		"SELECT catID, catName FROM Category WHERE catActive = 1 AND (catParentID IS NULL OR catParentID = 0);");			
		echo "<select name=\"dropdown_catID_Category\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo '<option value='.htmlspecialchars($row['catID']).'>'.htmlspecialchars($row['catName']).'</option>';
		}
		echo '<option>No Parent Category</option>';
		echo "</select>";
	}

	public function getDropdown_prodID_ProductOption(){
		$result = $this->dbConnection->query(
		"SELECT prodID,  prodName FROM Product;");			
		echo "<select name=\"dropdown_prodID_ProductOption\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['prodID'])."\">".htmlspecialchars($row['prodID'])."&nbsp;-&nbsp;".htmlspecialchars($row['prodName'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_prodOptID_ProductOptionValue(){
		$result = $this->dbConnection->query(
		"SELECT prodName, prodOptID, optValue 
		 FROM Product_Option
		 INNER JOIN Product ON Product_Option.prodID = Product.prodID ;");			
		echo "<select name=\"dropdown_prodOptID_ProductOptionValue\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['prodOptID'])."\">".htmlspecialchars($row['prodName'])."&nbsp;-->&nbsp;".htmlspecialchars($row['optValue'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_optValueID_ProductOptionValue(){
		$result = $this->dbConnection->query(
		"SELECT optValueID, optValueName, optName 
		 FROM Option_Value
		 INNER JOIN OptionType ON Option_Value.optTypeID = OptionType.optTypeID");			
		echo "<select name=\"dropdown_optValueID_ProductOptionValue\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['optValueID'])."\">".htmlspecialchars($row['optName'])."&nbsp;-->&nbsp;".htmlspecialchars($row['optValueName'])."</option>";
		}
		echo "</select>";
	}

	public function getDropdown_optionTypeID_OptionValue(){
		$result = $this->dbConnection->query(
		"SELECT optTypeID, optName FROM OptionType;");			
		echo "<select name=\"dropdown_optionTypeID_OptionValue\" size=\"1\" id=\"insert-dropdown\">";
		while( $row = $result->fetch_array())
		{
			echo "<option value=\"".htmlspecialchars($row['optTypeID'])."\">".htmlspecialchars($row['optTypeID'])."&nbsp;-&nbsp;".htmlspecialchars($row['optName'])."</option>";
		}
		echo "</select>";
	}


	//Inserts


	public function insertNews(){
		$titel = $_REQUEST['newsTitel'];
		$author = $_REQUEST['newsAuthor'];
		$story = $_REQUEST['newsStory'];
		$query = $this->dbConnection->query(
		"INSERT INTO NewsEntries (title, author, story) VALUES ('$titel', '$author', '$story');");

	}

	public function deleteNews(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM NewsEntries WHERE newsID =" . $ID);

	}

	public function deleteLogin(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM LoginData WHERE loginID=" . $ID);		
	}

	public function insertLogin(){
		$userLogin = $_REQUEST['insertusername'];
		$userDisplayName = $_REQUEST['insertdisplayname'];
		$userPassword = md5($_REQUEST['insertpassword']);
		$userActive = $_REQUEST['dropdown_status'];
		$query = $this->dbConnection->query(
		"INSERT INTO LoginData (userLogin, userDisplayName, userPassword, userActive) VALUES ('$userLogin', '$userDisplayName', '$userPassword', '$userActive');");
	}

	public function deleteSupplier(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Supplier WHERE supplID=" . $ID);		
	}

	public function deleteStatus(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Status WHERE statusID=" . $ID);		
	}

	public function deleteOptionType(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM OptionType WHERE optTypeID=" . $ID);		
	}

	public function deleteOptionValue(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Option_Value WHERE optValueID=" . $ID);		
	}

	public function deleteProductOption(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Product_Option WHERE prodOptID=" . $ID);		
	}

	public function deleteProductOptionValue(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Product_Option_Value WHERE prodOptValueID=" . $ID);		
	}

	public function deleteCategory(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Category WHERE catID=" . $ID);		
	}

	public function deleteProduct(){
		$ID = $_REQUEST['id'];
		$query = $this->dbConnection->query(
		"DELETE FROM Product WHERE prodID=" . $ID);		
	}

	public function insertSupplier(){
		$supplName = $_REQUEST['supplName'];
		$supplActive = $_REQUEST['supplActive'];
		$query = $this->dbConnection->query(
		"INSERT INTO Supplier (supplName,supplActive) VALUES('$supplName', '$supplActive');");		
	}

	public function insertStatus(){
		$statusName = $_REQUEST['statusName'];
		$statusColor = $_REQUEST['statusColor'];
		$query = $this->dbConnection->query(
		"INSERT INTO Status (statusName,statusColor) VALUES('$statusName', '$statusColor');");	
	}

	public function insertOptionValue(){
		$optValueName = $_REQUEST['optValueName'];
		$optionTypeID = $_REQUEST['dropdown_optionTypeID_OptionValue'];
		$query = $this->dbConnection->query(
		"INSERT INTO Option_Value (optValueName, optTypeID) VALUES('$optValueName', '$optionTypeID');");		
	}

	public function insertOptionType(){
		$optType = $_REQUEST['optType'];
		$optName = $_REQUEST['optName'];
		$query = $this->dbConnection->query(
		"INSERT INTO OptionType (optType, optName) VALUES('$optType', '$optName');");		
	}

	public function insertProductOptionValue(){
		$prodOptID = $_REQUEST['dropdown_prodOptID_ProductOptionValue'];
		$optValueID = $_REQUEST['dropdown_optValueID_ProductOptionValue'];
		$prodOptQuantity = $_REQUEST['prodOptQuantity'];
		$prodOptPrice = $_REQUEST['prodOptPrice'];
		$query = $this->dbConnection->query(
		"INSERT INTO Product_Option_Value (prodOptID, optValueID, prodOptQuantity, prodOptPrice) VALUES('$prodOptID', '$optValueID', '$prodOptQuantity', '$prodOptPrice');");		
	}

	public function insertProductOption(){
		$prodID = $_REQUEST['dropdown_prodID_ProductOption'];
		$optionValue = $_REQUEST['optionValue'];
		$prodOptRequired = $_REQUEST['prodOptRequired'];
		$query = $this->dbConnection->query(
		"INSERT INTO Product_Option (prodID, optValue, prodOptRequired) VALUES ('$prodID', '$optionValue', '$prodOptRequired');");		
	}

	public function insertProduct(){
		//Upload picture and rename it
		$dateityp = GetImageSize($_FILES['datei']['tmp_name']);
		if($dateityp[2] != 0)
		{
			if($_FILES['datei']['size'] <  10240000)
			{
				if ($dateityp[2] == 1) {
					$dateiendung = '.gif';
				}
				if ($dateityp[2] == 2) {
					$dateiendung = '.jpg';
				}
				if ($dateityp[2] == 3) {
					$dateiendung = '.png';
				}
				if ($dateityp[2] == 6) {
					$dateiendung = '.bmp';
				}
				$dateiname1 = $_REQUEST['dropdown_supplID_Product'];
				$dateiname2 = $_REQUEST['dropdown_catID_Product'];
				$dateiname3 = $_REQUEST['prodName'];
			 $dateinameneu = $dateiname1.'-'.$dateiname2.'-'.$dateiname3.$dateiendung;
			 move_uploaded_file($_FILES['datei']['tmp_name'], "upload/".$dateinameneu);
			 //echo "Das Bild wurde Erfolgreich nach upload/".$_FILES['datei']['name']." hochgeladen";
			}
			else
			{
				//echo "Das Bild darf nicht größer als 10000 kb sein ";
			}
		}
		else
		{
			//echo "Bitte nur Bilder im GIF, JPG, PNG, BMP oder SWF Format hochladen";
		}
		$supplID = $_REQUEST['dropdown_supplID_Product'];
		$catID = $_REQUEST['dropdown_catID_Product'];
		$prodName = $_REQUEST['prodName'];
		$prodDescriptionShort = $_REQUEST['prodDescriptionShort'];
		$prodDescription = $_REQUEST['prodDescription'];
		$prodImage = $dateinameneu;
		$statusID = $_REQUEST['dropdown_statusID_Product'];
		$prodActive = $_REQUEST['prodActive'];
		$prodMRSP = $_REQUEST['prodMRSP'];
		$prodPrice = $_REQUEST['prodPrice'];

		$query = $this->dbConnection->query(
		"INSERT INTO Product (supplID, catID, prodName, prodDescriptionShort, prodDescription, prodImage, statusID, prodActive, prodMRSP, prodPrice) VALUES ('$supplID', '$catID', '$prodName', '$prodDescriptionShort', '$prodDescription', '$prodImage', '$statusID', '$prodActive', '$prodMRSP', '$prodPrice');");		
	}

	public function insertCategory(){
		//Upload picture and rename it
		$dateityp = GetImageSize($_FILES['datei']['tmp_name']);
		if($dateityp[2] != 0)
		{
			if($_FILES['datei']['size'] <  10240000)
			{
				if ($dateityp[2] == 1) {
					$dateiendung = '.gif';
				}
				if ($dateityp[2] == 2) {
					$dateiendung = '.jpg';
				}
				if ($dateityp[2] == 3) {
					$dateiendung = '.png';
				}
				if ($dateityp[2] == 6) {
					$dateiendung = '.bmp';
				}
				$dateiname1 = $_REQUEST['dropdown_catID_Category'];
				$dateiname2 = $_REQUEST['catName'];
			 $dateinameneu = $dateiname1.'-'.$dateiname2.$dateiendung;
			 move_uploaded_file($_FILES['datei']['tmp_name'], "upload/".$dateinameneu);
			 //echo "Das Bild wurde Erfolgreich nach upload/".$dateinameneu." hochgeladen";
			 //echo "<script language=\"JavaScript\"><div onLoad=\"box_zugross();\"></div></script>";
			}
			else
			{
				//echo "Das Bild darf nicht grösser als 10000 kb sein ";
			}
		}
		else
		{
			//echo "Bitte nur Bilder im GIF, JPG, PNG, BMP oder SWF Format hochladen";
		}
		$catParentID = $_REQUEST['dropdown_catID_Category'];
		$catName = $_REQUEST['catName'];
		$catDescriptionShort = $_REQUEST['catDescriptionShort'];
		$catDescription = $_REQUEST['catDescription'];
		$catImage = $dateinameneu;
		$catActive = $_REQUEST['catActive'];
		$query = $this->dbConnection->query(
		"INSERT INTO Category (catParentID, catName, catDescriptionShort, catDescription, catImage, catActive) VALUES ('$catParentID', '$catName', '$catDescriptionShort', '$catDescription', '$catImage', '$catActive');");		
	}
	
	public function generateXML($product){
		
		//Get all nessesary details of a product
		$result = $this->dbConnection->query(
		"SELECT
		 Product.prodID,
		 Product.prodName,
		 Product.prodDescription,
		 Status.statusName,
		 Supplier.supplName,
		 Product.prodMRSP,
		 Product.prodPrice
		 FROM Product
		 INNER JOIN Category ON Product.catID = Category.catID
		 INNER JOIN Status ON Product.statusID = Status.statusID
		 INNER JOIN Supplier ON Product.supplID = Supplier.supplID
		 WHERE Product.prodID = $product");
		while($row = $result->fetch_array()){
			// create a dom document with encoding utf8
			$domtree = new DOMDocument('1.0', 'UTF-8');
			
			// create the root element of the xml tree
			$xmlRoot = $domtree->createElement("products");
			// append it to the document created
			$xmlRoot = $domtree->appendChild($xmlRoot);
			
			// create product element
			$p = $domtree->createElement("product");
			$p = $xmlRoot->appendChild($p);
			
			// create for each detail an element and append to product
			$p->appendChild($domtree->createElement('ID',$row['prodID']));
			$p->appendChild($domtree->createElement('Name',$row['prodName']));
			
			$p->appendChild($domtree->createElement('Description',$row['prodDescription']));
			$p->appendChild($domtree->createElement('Status',$row['statusName']));
			
			$p->appendChild($domtree->createElement('Supplier',$row['supplName']));
			$p->appendChild($domtree->createElement('MRSP',$row['prodMRSP']));
			$p->appendChild($domtree->createElement('BasePrice',$row['prodPrice']));
			
			// check for any product options			
			$options = $this->dbConnection->query(
			"SELECT DISTINCT
			 optValue,
			 Product.prodID,
			 Product_Option.prodOptRequired
			 FROM Product 
			 INNER JOIN Product_Option ON Product.prodID = Product_Option.prodID 
			 WHERE Product.prodID = " . $row['prodID'] . "
			 AND Product_Option.prodOptRequired = 1");
			
			if ($options->num_rows == true)
			{
				// if there are options append element to product
				$o = $domtree->createElement("Options");
				$o = $p->appendChild($o);
				
			 while($option = $options->fetch_array()){
			 	
			 	// list all values of an option an append
			 	$op = $domtree->createElement("Option");
			 	$op = $o->appendChild($op);
			 	// append option type as attribute to option elements
			 	$opAt = $domtree->createAttribute("Type");
			 	$opAt = $op->appendChild($opAt);
			 	// append option type (color, size) to option element as textnode
			 	$opText = $domtree->createTextNode($option['optValue']);
			 	$opText = $opAt->appendChild($opText);
			 	
			 	$optionValues = $this->dbConnection->query(
			 	"SELECT
 				 Product.prodID,
 			     prodPrice,
 				 prodOptPrice,
 				 prodName,
 				 optValue,
 				 optValueName,
 				 Product_Option.prodOptRequired
 				 FROM Product
 				 INNER JOIN Product_Option ON Product.prodID = Product_Option.prodID
 				 INNER JOIN Product_Option_Value ON Product_Option.prodOptID = Product_Option_Value.prodOptID
 				 INNER JOIN Option_Value ON Product_Option_Value.optValueID = Option_Value.optValueID
 				 INNER JOIN OptionType ON Option_Value.optTypeID = OptionType.optTypeID
 				 WHERE Product.prodID = " . $row['prodID'] . " AND optValue = '" . $option['optValue'] . "' AND prodOptRequired = 1
 				 ");
				
			 	//list option values
			 	if ($optionValues->num_rows == true){
			 		while($optionValue = $optionValues->fetch_array()){
			 			
			 			// append option type to option element
			 			$opVa = $domtree->createElement($optionValue['optValue']);
			 			$opVa = $op->appendChild($opVa);
			 			
			 			// append PriceUplift element
			 			$opVaPr = $domtree->createAttribute("PriceUpLift");
			 			$opVaPr = $opVa->appendChild($opVaPr);
			 			
			 			//append PriceUplift Value as textnode
			 			$opVaPrVal = $domtree->createTextNode($optionValue['prodOptPrice']);
			 			$opVaPrVal = $opVaPr->appendChild($opVaPrVal);
			 			
			 			//append Option Value as textnoce
			 			$opVaText = $domtree->createTextNode($optionValue['optValueName']);
			 			$opVaText = $opVa->appendChild($opVaText);
			 			}
			 		}
			 	}
			}
		}

		/* Make XML downloadable */
		header('Content-type: "text/xml"; charset="utf8"');
		header('Content-disposition: attachment; filename="'.$product.'.xml"');

		/* get the xml printed */
		echo $domtree->saveXML();
	}

}

?>
